package jdbc.dao;

import com.sun.xml.internal.bind.v2.model.core.ID;
import jdbc.domian.User;
import utils.JDBCUtils;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by guo on 2017/9/12.
 */
public class UserDao {
public List<User> findALL() throws ClassNotFoundException, SQLException {
    List<User> linkedList = null;
    ResultSet set = null;
    Statement statement = null;
    Connection con = JDBCUtils.getConetion();
    try {

        statement = con.createStatement();
        linkedList = new LinkedList<>();
        String sql = "select * from user";
        set = statement.executeQuery(sql);
        while (set.next()) {
            User user = new User();
            user.setId(set.getInt(1));
            user.setUsername(set.getString(2));
            user.setPassword(set.getString(3));
            user.setEmail(set.getString(4));
            user.setPhone(set.getString(5));
            linkedList.add(user);
        }

        return linkedList;
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (set != null)
            try {
                set.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if (statement != null)
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            con.close();
    }
    return null;
//    Class.forName("com.mysql.jdbc.Driver");
//    Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3307/task","root","123456");

}
public boolean insert(User user) throws SQLException {
//    Class.forName("com.mysql.jdbc.Driver");
//    Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3307/task","root","123456");
//    Connection con=JDBCUtils.getConetion();
//    Statement statement=con.createStatement();
//    String sql="insert into  user values (6,'sun','354354343112','566563543214@qq.com','254343440')";
//    statement.executeUpdate(sql);
//    return true;
    Connection con = JDBCUtils.getConetion();
    String sql = "insert into user(id,username,passwprd) values(?,?,?)";
    PreparedStatement statement = null;
    try {
        statement = con.prepareStatement(sql);
        statement.setInt(1, user.getId());
        int num = statement.executeUpdate();
        if (num == 1) {
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
     if(statement!=null)
     {
         statement.close();
     }
    }

return true;
}
public boolean update() throws ClassNotFoundException, SQLException {
//    Class.forName("com.mysql.jdbc.Driver");
//    Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3307/task","root","123456");
    Connection con=JDBCUtils.getConetion();
    Statement statement=con.createStatement();
    String sql="update user set username ='li' where id=1 ";
    statement.executeUpdate(sql);
    return true;
}
public boolean delete() throws SQLException, ClassNotFoundException {
//    Class.forName("com.mysql.jdbc.Driver");
//    Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3306/task","root","123456");
    Connection con=JDBCUtils.getConetion();
    Statement statement=con.createStatement();
    String sql="delete from user where username='chen'";
    statement.executeUpdate(sql);
    return true;
}
    public User findById(int id) throws ClassNotFoundException, SQLException {
        User user = new User();
        ResultSet set = null;
        PreparedStatement statement = null;
        Connection con = JDBCUtils.getConetion();
        try {



            String sql = "select * from user where id=?";
            statement = con.prepareStatement(sql);
               statement.setInt(1, id);
               set=statement.executeQuery();
               while (set.next())
               {
                   user.setId(set.getInt(1));
                   user.setUsername(set.getString(2));
                   user.setEmail(set.getString(3));
                   return user;
               }
            }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            if (set != null)
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (statement != null)
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        con.close();
        return null;
    }
}
